package videostoreapp.dataobjects;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.LinkedList;
import java.util.List;

import videostoreapp.Catalogue;
import videostoreapp.Constants;

public class CatalogueDO implements Catalogue{

	Connection conn = null; 
	PreparedStatement 
		psNewVid = null, 
		psUpdVid = null,
		psDelVid = null,
		psGetVidId = null,
		psGetVidCatNo = null,
		psGetVidDir = null,
		psGetVidGenre = null;
	
	public CatalogueDO(){
		try {
			conn = Constants.getConnection();
			
			psNewVid = conn.prepareStatement(
					"INSERT INTO Catalogue (catalogue_no,title,director,genre,price) VALUES (?, ?, ?, ?, ?)",
					Statement.RETURN_GENERATED_KEYS);
			
			psUpdVid = conn.prepareStatement(
					"UPDATE Catalogue SET " +
					"	catalogue_no = ?, " +
					"	title = ?, " +
					"	director = ?, " +
					"   genre = ?, " +
					"   price = ? " +
					" WHERE ID = ?");
			
			psDelVid = conn.prepareStatement(
					"DELETE FROM Catalogue WHERE ID = ?");
			
			psGetVidId = conn.prepareStatement(
					"SELECT id, catalogue_no,title,director,genre,price" +
					" FROM Catalogue WHERE ID = ?");
			
			psGetVidCatNo = conn.prepareStatement(
					"SELECT id,catalogue_no,title,director,genre,price" +
					" FROM Catalogue WHERE catalogue_no = ?");
			
			psGetVidDir = conn.prepareStatement(
					"SELECT id,catalogue_no,title,director,genre,price" +
					" FROM Catalogue WHERE director = ?");
			
			psGetVidGenre = conn.prepareStatement(
					"SELECT id,catalogue_no,title,director,genre,price" +
					" FROM Catalogue WHERE genre = ?");
			
		} catch (SQLException e) {
			Constants.SQLExceptionHandler(e);
		}
	}
	
	public int createVideo(CatalogueInfo video) {
		int cId = -1;
		if(video != null){
			try{
				psNewVid.setString(1, video.catNo);
				psNewVid.setString(2, video.title);
				psNewVid.setString(3, video.director);
				psNewVid.setString(4, video.genre);
				psNewVid.setFloat(5, video.price);
				
				if(psNewVid.executeUpdate() >0){
					ResultSet rs = psNewVid.getGeneratedKeys();
					if(rs.next()){
						cId = rs.getInt(1);
					}		
					rs.close();
				}

			}catch(SQLException sqe){
				Constants.SQLExceptionHandler(sqe);
			}
		}
		return cId;
	}

	public void deleteVideo(int id) {
		
		try {
			psDelVid.setInt(1, id);
			psDelVid.executeUpdate();
		} catch (SQLException sqe) {
			Constants.SQLExceptionHandler(sqe);
		}
	}

	public CatalogueInfo getVideoByCatalogueNumber(String catNo) {
		CatalogueInfo cif = null;
		try {
			psGetVidCatNo.setString(1, catNo);
			ResultSet rs = psGetVidCatNo.executeQuery();
			
			//(catalogue_no,title,director,genre,price)
			if(rs.next()){
				cif = new CatalogueInfo(
						rs.getInt("id"),
						rs.getString("catalogue_no"),
						rs.getString("title"),
						rs.getString("director"),
						rs.getString("genre"),
						rs.getFloat("price")
				);
			}
			rs.close();
		} catch (SQLException sqe) {
			Constants.SQLExceptionHandler(sqe);
		}
		
		return cif;
	}

	public CatalogueInfo getVideoById(int id) {
		CatalogueInfo cif = null;
		try {
			psGetVidId.setInt(1, id);
			ResultSet rs = psGetVidId.executeQuery();
			
			//(catalogue_no,title,director,genre,price)
			if(rs.next()){
				cif = new CatalogueInfo(
						rs.getInt("id"),
						rs.getString("catalogue_no"),
						rs.getString("title"),
						rs.getString("director"),
						rs.getString("genre"),
						rs.getFloat("price")
				);
			}
			rs.close();
		} catch (SQLException sqe) {
			Constants.SQLExceptionHandler(sqe);
		}
		
		return cif;
	}

	public List<CatalogueInfo> getVideosByDirector(String director) {
		LinkedList<CatalogueInfo> vids = new LinkedList<CatalogueInfo>();
		
		if(director != null){
			try {
				psGetVidDir.setString(1, director);
				ResultSet rs = psGetVidDir.executeQuery();

				//(catalogue_no,title,director,genre,price)
				while(rs.next()){
					 vids.add(
						new CatalogueInfo(
							rs.getInt("id"),
							rs.getString("catalogue_no"),
							rs.getString("title"),
							rs.getString("director"),
							rs.getString("genre"),
							rs.getFloat("price"))
					);
				}
				rs.close();
			} catch (SQLException sqe) {
				Constants.SQLExceptionHandler(sqe);
			}
		}
		return vids;
	}

	public List<CatalogueInfo> getVideosByGenre(String genre) {
		LinkedList<CatalogueInfo> vids = new LinkedList<CatalogueInfo>();
		
		if(genre != null){
			try {
				psGetVidGenre.setString(1, genre);
				ResultSet rs = psGetVidGenre.executeQuery();

				//(catalogue_no,title,director,genre,price)
				while(rs.next()){
					 vids.add(
						new CatalogueInfo(
							rs.getInt("id"),
							rs.getString("catalogue_no"),
							rs.getString("title"),
							rs.getString("director"),
							rs.getString("genre"),
							rs.getFloat("price"))
					);
				}
				rs.close();
			} catch (SQLException sqe) {
				Constants.SQLExceptionHandler(sqe);
			}
		}
		return vids;
	}

	public void modifyVideo(CatalogueInfo video) {
		if(video != null){
			
			try {
				psUpdVid.setString(1, video.catNo);
				psUpdVid.setString(2, video.title);
				psUpdVid.setString(3, video.director);
				psUpdVid.setString(4, video.genre);
				psUpdVid.setFloat(5, video.price);
				psUpdVid.setInt(6, video.id);
				
				psUpdVid.executeUpdate();
				
			} catch (SQLException sqe) {
				Constants.SQLExceptionHandler(sqe);
			}
		}
		
	}

}
